package com.scalar.chiptrack.operations.shippingRequest.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.dao.PartNumber;
import com.scalar.chiptrack.operations.process.dao.ProductDao;
import com.scalar.chiptrack.operations.shippingRequest.ShippingRequest;
import com.scalar.chiptrack.operations.shippingRequest.ShippingRequestLot;
import com.scalar.chiptrack.operations.shippingRequest.ShippingRequestMailAck;
import com.scalar.chiptrack.operations.vendor.dao.ShipperDAO;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;

public class ShippingRequestDAO implements Constants
{

    public static ArrayList getProductList( String processType ) throws ScalarException
    {
        ArrayList partNumberSelectList = new ArrayList();
        ArrayList partNumberList = null;
        PartNumber partNumber = null;

        partNumberList = ProductDao.getPartNumber( processType );
        for ( int i = 0; i < partNumberList.size(); i++ )
        {
            partNumber = ( PartNumber ) partNumberList.get( i );
            partNumberSelectList.add( new org.apache.struts.util.LabelValueBean( partNumber.getPartNumber(), partNumber.getProcessStepId() ) );
        }

        return partNumberSelectList;
    }

    /*
	 * All Possiable Years
	 */
    public static ArrayList getYearList()
    {
        int year = 2000;
        ArrayList yearList = new ArrayList();
        for ( int i = 0; i <= 15; i++)
        {
            yearList.add( new LabelValueBean( year+"", year+"" ) );
            year++;
        }
        return yearList;
    }

    /*
     * All Possiable Status
     */
    public static ArrayList getStatusList()
    {
		ArrayList statusList = new ArrayList();
		statusList.add( new LabelValueBean( "Issued", "Issued" ) );
		statusList.add( new LabelValueBean( "Approved", "Approved" ) );
		statusList.add( new LabelValueBean( "Closed", "Closed" ) );
        statusList.add( new LabelValueBean( "Shipped", "Shipped" ) );
		statusList.add( new LabelValueBean( "Cancelled", "Cancelled" ) );
        return statusList;
    }

    public static ArrayList loadClassStroeLocations( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection        conn      = null;
	    PreparedStatement pstmt     = null;
	    ResultSet         rs        = null;
        ArrayList         locations = null;

	    try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get("operations.process.vendorLocation.ClassStoreLocations") );
            rs = pstmt.executeQuery();

            locations = new ArrayList();

            while ( rs.next() )
            {
                locations.add( new org.apache.struts.util.LabelValueBean( rs.getString("LOCATION_NAME"), rs.getInt("LOCATION_ID")+"" ) );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException("Could not load Class Store locations", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}
        return locations;
    }

    public static ArrayList getLotDetails(  ShippingRequest shippingRequest )  throws ScalarException
    {
        ArrayList classStoreLotsList = new ArrayList();
        ShippingRequestLot shippingRequestLot = null;
        Connection conn 		= null;
		CallableStatement cstmt = null;
		ResultSet rs 			= null;

		try
		{
			conn = DataSource.getConnection();
			cstmt = conn.prepareCall( "{ call sp_load_class_store_lots_for_search( ?,?,? ) }" );
            cstmt.setInt( 1, shippingRequest.getProcessStepId() );
            cstmt.setInt( 2, shippingRequest.getFromLocationId() );
            cstmt.setInt( 3,  shippingRequest.getShipRequestId() );
            rs 	= cstmt.executeQuery();

			while( rs.next() )
			{

                shippingRequestLot = new ShippingRequestLot();
                shippingRequestLot.setLotId( rs.getInt("Lot_Id"));
                shippingRequestLot.setLotNumber( rs.getString( "Lot_Number" ) );
                shippingRequestLot.setAvailableQty( rs.getInt( "Qty_Available" ) );
                shippingRequestLot.setPulledQty( rs.getInt( "Qty_Pulled" ) );
                if( shippingRequestLot.getPulledQty()  > 0 )
                {
                    shippingRequestLot.setChecked( true );
                }
                //This is to check whether the Lot is already reached to Ship to Vendor, disabling the Lot info
                String shipRequestLotStatus = rs.getString( "SHIP_REQ_LOT_STATUS");
                if( "Closed".equalsIgnoreCase( shipRequestLotStatus ) )
                {
                    shippingRequestLot.setDisabled( true );
                }

                classStoreLotsList.add( shippingRequestLot );
			}
		}
		catch( Exception e )
		{
            e.printStackTrace();
            Logger.error( "Error while retreiving the existing Class Store lots", e );
			throw new ScalarException ("Error while retreiving the existing Class Store lots");
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
            }

			if (cstmt != null)
			{
				try { cstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
        return classStoreLotsList;
    }

    public static String genarateAutomatedShipRequestNumber( int incrementBy ) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rs 			= null;
        String shipReqNo 		= null;
        String finalshipReqNo 	= null;
        String number 				= null;
        String shipReqPrefix 	= null;
        int shipReqLength 		= 0;

        try
        {
            conn  = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getSalesOrderInfo" ) );
            rs = pstmt.executeQuery();
            if( rs.next())
            {
                shipReqPrefix = rs.getString( "Shipping_Request_Prefix" );
                shipReqLength = rs.getInt( "Shipping_Request_Length" );
            }
            rs.close();
            pstmt.close();
            pstmt = null;

            if(conn == null || conn.isClosed() )
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT SHIP_REQ_ID, SHIP_REQ_NO FROM SHIPPING_REQUEST_TABLE WHERE SHIP_REQ_ID = ( SELECT MAX( SHIP_REQ_ID ) FROM SHIPPING_REQUEST_TABLE )" );
            rs    = pstmt.executeQuery();
            if ( rs.next() )
            {
                shipReqNo = rs.getString( "SHIP_REQ_NO" );
            }
            else
            {
            shipReqNo = "0";
            }

            try
            {
                if( shipReqNo != null )
                {
                    int indexValue 	= shipReqNo.indexOf("-");
                    number 			= shipReqNo.substring(indexValue+1);
                    String no 		= (Long.parseLong (number) + incrementBy )+"";
                    int noLength 	= no.length();
                    int totalLength = shipReqLength - noLength ;
                    if( totalLength > 0 )
                    {
                        for(int  i = 0; i < totalLength ; i++  )
                        {
                            no = "0"+no;
                        }
                    }

                    if( shipReqPrefix != null && shipReqPrefix.trim().length() != 0 )
                    {
                        finalshipReqNo = shipReqPrefix+"-"+no ;
                    }
                    else
                    {
                        finalshipReqNo = no;
                    }
                }
            }
            catch (NumberFormatException nfe)
            {
                finalshipReqNo = (rs.getInt( "SHIP_REQ_ID" ) + incrementBy )+"" ;
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Automated Ship Request No from database", sqlex);
        }

        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return finalshipReqNo;
    }

    public static String getVendorName( int locationId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        String vendorName       = null;
        ResultSet rset          = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT L_VENDOR_NAME From LOCATION WHERE LOCATION_ID = ?" );
            pstmt.setInt( 1, locationId );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                vendorName = rset.getString( "L_VENDOR_NAME" );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all Lotnumbers from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return vendorName;
    }

    public static int getVendorIdByLocationId( int locationId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        int vendorId       = -1;
        ResultSet rset          = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT VENDOR_ID From LOCATION WHERE LOCATION_ID = ?" );
            pstmt.setInt( 1, locationId );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                vendorId = rset.getInt( "VENDOR_ID" );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load Vendor Id from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return vendorId;
    }
    /**
     *
     * @param dbTransaction
     * @param shipRequest
     * @param userID
     * @throws ScalarException
     */

    public static void saveShipRequestDetails( DBTransaction  dbTransaction, ShippingRequest shipRequest, String userID ) throws ScalarException
    {
        Connection conn 		= null;
		CallableStatement cstmt = null;
        boolean success = false;

        try
		{
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall( "{ call sp_save_ship_request_details( ?,?,?,?,?,?,?,?,?,?,? ) }" );
            cstmt.setInt(1, shipRequest.getShipRequestId() );
            cstmt.setString(2, shipRequest.getShipReqNumber() );
            cstmt.setString( 3, shipRequest.getShipReqDate() );
            cstmt.setInt( 4, shipRequest.getProcessStepId() );
            cstmt.setInt( 5, shipRequest.getFromLocationId() );
            cstmt.setInt( 6, shipRequest.getShipTolocationId() );
            cstmt.setInt( 7, shipRequest.getShipperId() );
            cstmt.setString( 8, shipRequest.getSpecialInstr() );
            cstmt.setString( 9, shipRequest.getStatus() );
            cstmt.setString( 10, userID );
            cstmt.registerOutParameter( 11, Types.INTEGER );

			cstmt.executeUpdate();
            shipRequest.setShipRequestId( cstmt.getInt( 11 ) ) ;
            success = true;
		}
		catch( Exception e )
		{
            success = false;
            e.printStackTrace();
            Logger.error( "Error while saving the Ship Request details", e );
			throw new ScalarException ("Error while saving the Ship Request details");
		}
		finally
		{
            if ( dbTransaction == null )
            {
                if( success == true )
                {
                    try
                    {
                        conn.commit();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while commiting the Ship Request details Confirm" );
                    }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while rolling back the Ship Request details Confirm" );
                    }
                }
            }

            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
		}
    }

    /**
     * Saves the list of ship Request lot objects in the database
     *
     * @param shipRequest
     */
    public static void saveShipRequestLotDetails( DBTransaction dbTransaction , ShippingRequest shipRequest ) throws ScalarException
    {
        Connection conn 		= null;
		CallableStatement cstmt = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        Iterator shipReqLotListIterator = shipRequest.getLotList().iterator();
        ShippingRequestLot shipReqLot = null;

		try
		{
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall( "{ call sp_save_ship_request_lot_details( ?,?,?,?,?,? ) }" );
            while( shipReqLotListIterator.hasNext() )
            {
                shipReqLot = ( ShippingRequestLot )shipReqLotListIterator.next();
                cstmt.setInt( 1, shipRequest.getShipRequestId() );
                cstmt.setInt( 2, shipReqLot.getLotId() );
                cstmt.setInt( 3, shipReqLot.getPulledQty() );
                cstmt.setInt( 4, shipReqLot.getAvailableQty() );
                cstmt.setString( 5, shipRequest.getShipReqDate() );
                cstmt.setString( 6, shipRequest.getToVendorName() );
                cstmt.executeUpdate();
            }

            try{ cstmt.close();}catch( SQLException ignore ){}

            //TO CLOSE SHIPPING REQUEST IF ALL LOTS ARE SHIPPED
            pstmt = conn.prepareStatement( "UPDATE Shipping_Request_table SET STATUS = 'Closed' WHERE Ship_Req_Id = ? AND ((SELECT COUNT(*) FROM Shipping_Request_Lot_Table WHERE Ship_Req_Id = ? AND Ship_Req_Lot_Status = 'Approved' ) = 0)" );
            pstmt.setInt ( 1, shipRequest.getShipRequestId() );
            pstmt.setInt ( 2, shipRequest.getShipRequestId() );
            pstmt.executeUpdate();

            success = true;
		}
		catch( Exception e )
		{
            e.printStackTrace();
            Logger.error( "Error while saving the Ship Request lot details", e );
			throw new ScalarException ("Error while saving the Ship Request lot details");
		}
		finally
		{
            if ( dbTransaction == null )
            {

                if( success == true )
                {
                    try
                    {
                        conn.commit();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while commiting the Ship Request lot details save" );
                    }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while rolling back the Ship Request lot details save" );
                    }
                }
            }

            if (cstmt != null)
			{
				try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ) {}
            }
            if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
    }

    /**
     *
     *
     * @param dbTransaction
     * @return ArrayList
     * @throws ScalarException
     */
    public static ArrayList getAllShipRequestDetails( DBTransaction dbTransaction, ShippingRequest shippingRequest  ) throws ScalarException
    {
        ShippingRequest shipRequest = null;
		Connection conn 		    = null;
		PreparedStatement pstmt     = null;
		ResultSet rs 			    = null;
        ArrayList allshipReqList    = new ArrayList();
		try
		{
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt 	= conn.prepareStatement( "SELECT SRT.SHIP_REQ_ID, SHIP_REQ_NO, SHIP_REQ_DATE,P.PART_NUMBER, L.L_VENDOR_NAME,INSTRUCTIONS, STATUS, SHIPPER FROM SHIPPING_REQUEST_TABLE SRT,LOCATION L, PRODUCT P WHERE SRT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND L.LOCATION_ID = SRT.TO_VENDOR "+ getQueryStringForShipRequestSearch( shippingRequest ) + "ORDER BY SHIP_REQ_ID DESC " );
            rs 		= pstmt.executeQuery();

			while ( rs.next() )
			{
                shipRequest = new ShippingRequest();
                shipRequest.setShipRequestId( rs.getInt( "SHIP_REQ_ID" ) );
                shipRequest.setShipReqNumber( rs.getString( "SHIP_REQ_NO" ) );
                shipRequest.setShipReqDate( rs.getString( "SHIP_REQ_DATE" ) );
                shipRequest.setPartNumber( rs.getString( "PART_NUMBER" ) );
                shipRequest.setToVendorName( rs.getString( "L_VENDOR_NAME" ) ) ;
                shipRequest.setSpecialInstr( rs.getString( "INSTRUCTIONS" ) );
                shipRequest.setStatus( rs.getString( "STATUS" ) );
                shipRequest.setShipperId( rs.getInt( "SHIPPER" ) );

                allshipReqList.add( shipRequest );
			}

		}
		catch( Exception e )
		{
            Logger.error( "Error while retreiving the sort release details", e );
            e.printStackTrace();
			throw new ScalarException ("Error while retreiving the sort release details");
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}


        return allshipReqList;
    }

    /**
     * TO LOAD THE SHIP REQUEST DETAILS
     * @param dbTransaction
     * @param shippingRequest
     * @return
     * @throws ScalarException
     */
    public static ShippingRequest loadShipRerquestDetails( DBTransaction dbTransaction, ShippingRequest shippingRequest ) throws ScalarException
    {
        ShippingRequest tempShippingRequest = null;
        Connection conn                     = null;
        PreparedStatement pstmt             = null;
        ResultSet rset                      = null;

        try
		{
            if( dbTransaction == null )
            {
                conn	= DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt 	= conn.prepareStatement( "SELECT SR.SHIP_REQ_ID, SR.SHIP_REQ_NO, SR.SHIP_REQ_DATE, SR.FROM_VENDOR, SR.TO_VENDOR,SR.PROCESSSTEP_ID, SR.SHIPPER, SR.INSTRUCTIONS,P.PART_NUMBER FROM SHIPPING_REQUEST_TABLE SR, PRODUCT P WHERE SR.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND SR.SHIP_REQ_ID = ?" );
            pstmt.setInt( 1, shippingRequest.getShipRequestId() );
			rset 		= pstmt.executeQuery();

			if ( rset.next() )
			{
                tempShippingRequest = new ShippingRequest();
                tempShippingRequest.setShipRequestId( rset.getInt( "SHIP_REQ_ID" ) ) ;
                tempShippingRequest.setShipReqNumber( rset.getString( "SHIP_REQ_NO" ) ) ;
                tempShippingRequest.setShipReqDate( StringUtils.formatDateToString( rset.getDate ( "SHIP_REQ_DATE" ), "MM/dd/yyyy" ) ) ;
                tempShippingRequest.setFromLocationId( rset.getInt( "FROM_VENDOR" ) ) ;
                tempShippingRequest.setShipTolocationId( rset.getInt( "TO_VENDOR" ) ) ;
                tempShippingRequest.setProcessStepId( rset.getInt( "PROCESSSTEP_ID") );
                tempShippingRequest.setShipperId( rset.getInt( "SHIPPER" ) ) ;
                tempShippingRequest.setSpecialInstr( rset.getString( "INSTRUCTIONS" ) ) ;
                tempShippingRequest.setPartNumber( rset.getString( "PART_NUMBER" ) );
                //tempShippingRequest.setTotalQty(rset.getInt("") );
                tempShippingRequest.setShipperName( ShipperDAO.getShipperNameById( tempShippingRequest.getShipperId() ) );
			}

		}
		catch( Exception e )
		{
            Logger.error( "Error while retreiving the ship request details", e );
            e.printStackTrace();
			throw new ScalarException ("Error while retreiving the ship request details");
		}
		finally
		{
			if (rset != null)
			{
				try { rset.close(); } catch (SQLException ignore) {}
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

        return tempShippingRequest;


    }
    /**
     * TO LOAD THE LOT DETAILES OF SHIP REQUEST
     * @param dbTransaction
     * @param shippingRequest
     * @throws ScalarException
     */

    public static void loadShippingRequestLotDetails( DBTransaction dbTransaction, ShippingRequest shippingRequest ) throws ScalarException
    {
        ArrayList shipRequestLotList = new ArrayList();
        Connection conn                     = null;
        PreparedStatement pstmt             = null;
        ResultSet rset                      = null;
        ShippingRequestLot shippingRequestLot = null;

        try
        {
            if( dbTransaction == null )
            {
                conn	= DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt 	= conn.prepareStatement( "SELECT SHIP_REQ_ID, SRL.LOT_ID, LT.LOT_NUMBER, SRL.QTY_PULLED,SRL.AVAILABLE_QTY FROM SHIPPING_REQUEST_LOT_TABLE SRL, LOT_TABLE LT WHERE SRL.LOT_ID = LT.LOT_ID AND SRL.SHIP_REQ_ID = ?" );
            pstmt.setInt( 1, shippingRequest.getShipRequestId() );
            rset 		= pstmt.executeQuery();
            int totalQtyPulled = 0;
            while ( rset.next() )
            {
               shippingRequestLot  = new ShippingRequestLot();
               shippingRequestLot.setLotId( rset.getInt("LOT_ID") );
               shippingRequestLot.setLotNumber( rset.getString("LOT_NUMBER") );
               shippingRequestLot.setPulledQty( rset.getInt("QTY_PULLED") );
               shippingRequestLot.setAvailableQty( rset.getInt("AVAILABLE_QTY") );
               shippingRequestLot.setChecked( true );
               totalQtyPulled = totalQtyPulled + shippingRequestLot.getPulledQty();
               shipRequestLotList.add( shippingRequestLot );
            }

            shippingRequest.setLotList( shipRequestLotList );
            shippingRequest.setTotalQty( totalQtyPulled );

        }
        catch( Exception e )
        {
            Logger.error( "Error while retreiving the ship request details", e );
            e.printStackTrace();
            throw new ScalarException ("Error while retreiving the ship request details");
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

    }
    public static void loadShipAlertMails ( DBTransaction dbTransaction, ShippingRequest shippingRequest ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        StringBuffer emails = new StringBuffer();
        boolean success = false;

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT EMAIL FROM ADDRESS ADDR WHERE ADDR.ADDRESS_ID IN ( SELECT A.ADDRESS_ID FROM VENDOR V, VENDOR_CONTACT_PERSONS VCP, ADDRESS A, LOCATION L WHERE ( V.VENDOR_ID = VCP.VENDOR_ID AND  VCP.ADDRESS_ID = A.ADDRESS_ID AND VCP.VCP_IS_ACTIVE = 0 AND VCP_TYPE = 'Shipping' AND V.VENDOR_ID = L.Vendor_ID AND L.Location_ID IN (?,?) ) ) OR ADDR.ADDRESS_ID IN ( SELECT ADDRESS_ID FROM LOCATION WHERE LOCATION_ID IN (?,?) ) UNION SELECT Lot_Flow_Emails AS EMAIL FROM NOTIFICATION_EMAILS_TABLE" );
            pstmt.setInt( 1, shippingRequest.getFromLocationId() );
            pstmt.setInt( 2, shippingRequest.getShipTolocationId() );
            pstmt.setInt( 3, shippingRequest.getFromLocationId() );
            pstmt.setInt( 4, shippingRequest.getShipTolocationId() );
            rset = pstmt.executeQuery();
            while( rset.next() )
            {
                emails.append( rset.getString( "EMAIL") ).append( Mailer.EMAIL_SEPARATOR );
            }

            shippingRequest.setShipAlerts( emails.toString().replaceAll( ",,", ",") );
            success = true;
        }
        catch (Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not fetch ship alert mails", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                           conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException ignore ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){}
            }
        }
    }
    public static void sendShipRequestMail ( ShippingRequest shippingRequest, String fromEmail ) throws ScalarException
    {
        try
        {
            String filename = ShippingRequestMailAck.getShipRequestPDFAck( shippingRequest );
            String toEmails = shippingRequest.getShipAlerts();
            String[] mailAttachments = new String[]{filename};
            String subject = "Ship Request - "+shippingRequest.getShipReqNumber();
            // SEND Empty MESSAGE
            Mailer.send ( fromEmail, toEmails, null, null, subject, null, mailAttachments, Mailer.CONTENT_TYPE_HTML, false );
        }
        catch( Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Exception while sending Ship Request Alert", e );
        }
    }
    public static ShippingRequest getShippingRequest ( DBTransaction dbTransaction, int shippingRequestId ) throws ScalarException
    {
        ShippingRequest shippingRequest  = new ShippingRequest();
        shippingRequest.setShipRequestId( shippingRequestId );
        shippingRequest = ShippingRequestDAO.loadShipRerquestDetails( dbTransaction, shippingRequest );
        shippingRequest.setFromVendorName( ShippingRequestDAO.getVendorName( shippingRequest.getFromLocationId() ) );
        //retriving From VENDOR ADDDRESS AND EMAIL
        Address fromAddress = getVendorAddress( shippingRequest.getFromLocationId() ) ;
        if ( fromAddress != null )
        {
            shippingRequest.setFromVendorAddress( fromAddress );
        }
        //retriving From VENDOR ADDDRESS AND EMAIL
        Address toAddress = getVendorAddress( shippingRequest.getShipTolocationId() ) ;
        if ( toAddress != null )
        {
            shippingRequest.setToVendorAddress( toAddress );
        }
        //SHOW ALSO TO VENDOR ADDRESS AND EMAIL
        shippingRequest.setToVendorName( ShippingRequestDAO.getVendorName( shippingRequest.getShipTolocationId() ) );
        ShippingRequestDAO.loadShippingRequestLotDetails( dbTransaction, shippingRequest );
        shippingRequest.setShippersList( ShipperDAO.loadShippersForVendor( dbTransaction, ShippingRequestDAO.getVendorIdByLocationId( shippingRequest.getFromLocationId() ) ) );
        ShippingRequestDAO.loadShipAlertMails( dbTransaction, shippingRequest );
        return shippingRequest;
    }

    /**
     * Constructs the query for returning the required ship Request
     *
     * @param shippingRequest
     * @return query
     */
    private static String getQueryStringForShipRequestSearch( ShippingRequest shippingRequest )
    {
        StringBuffer query = new StringBuffer();
        if ( shippingRequest != null )
        {
            if( shippingRequest.getProcessStepId() != -1 )
            {
                query.append( " AND SRT.PROCESSSTEP_ID = " + shippingRequest.getProcessStepId() );
            }
            if( shippingRequest.getShipTolocationId() != -1 )
            {
                query.append( " AND SRT.TO_VENDOR = " + shippingRequest.getShipTolocationId() );
            }
            if( ! ("-1".equalsIgnoreCase( shippingRequest.getStatus() ) )  )
            {
                query.append( " AND SRT.STATUS = '" + shippingRequest.getStatus()+"'" );
            }
            if( shippingRequest.getYear() != -1  )
            {
                query.append( " AND DATEPART( yyyy, SRT.SHIP_REQ_DATE ) = " + shippingRequest.getYear() );
            }
        }

        return query.toString();
    }

    /**
    * Returns VendorAddress for this location
    *
    * @param locationId
    * @return
    * @throws ScalarException
    */
    public static Address getVendorAddress (int locationId) throws ScalarException {

	Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Address address = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.lcoation.getVendorAddressByLocationId"));
            pstmt.setInt (1, locationId);

            rs = pstmt.executeQuery();

            if (rs.next() == false) {
                return address; // return null
            }

            address = new Address(rs.getInt ("Address_ID"),
                             rs.getString("Address_1"),
                             rs.getString("Address_2"),
                             rs.getString("Address_3"),
                             rs.getString( "Address_4"),
                             rs.getString("City"),
                             rs.getString("State"),
                             rs.getString("Zipcode"),
                             rs.getString("Country"),
                             rs.getString("Phone_Number"),
                             rs.getString("Fax_Number"),
                             rs.getString("Email"),
                             rs.getString( "Contact_Person" ),
                             rs.getString( "Cell_Number" ));
        }
        catch (SQLException se) {
            se.printStackTrace();
            throw new ScalarException ("Could not get Address for location ID " + locationId, se);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp){}
            }
        }

        return address;
    }

    /**
     * Updates Shiping Request status
     *
     * @param dbTransaction
     * @param shippingRequest
     * @param userID
     * @throws ScalarException
     */
    public static void updateShipRequestStatus( DBTransaction dbTransaction, ShippingRequest shippingRequest, String userID ) throws ScalarException
    {
        Connection conn = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        try
		{
            if( dbTransaction == null )
            {
			    conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "UPDATE SHIPPING_REQUEST_TABLE SET STATUS = ?, LAST_MODIFIED_BY = ?, LAST_MODIFIED = CURRENT_TIMESTAMP WHERE SHIP_REQ_ID = ?" );
            pstmt.setString( 1, shippingRequest.getStatus() );
            pstmt.setString( 2, userID );
            pstmt.setInt( 3, shippingRequest.getShipRequestId() );
			pstmt.executeUpdate();

            success = true;
		}
		catch( Exception e )
		{
            e.printStackTrace();
            Logger.error( "Error while updating the Ship Request status", e );
			throw new ScalarException ("Error while updating the Ship Request status");
		}
		finally
		{
            if( dbTransaction == null )
            {
                if( success == true )
                {
                    try
                    {
                        conn.commit();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while commiting the Ship Request details save" );
                    }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while rolling back the Ship Request details save" );
                    }
                }
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
    }


    /**
     * Saves the list of Cancel ship Request lot objects in the database
     *
     * @param shipRequest
     */
    public static void cancelShippingRequest( DBTransaction dbTransaction , ShippingRequest shipRequest ) throws ScalarException
    {
        Connection conn 		= null;
		CallableStatement cstmt = null;
        boolean success = false;
        Iterator cancelshipReqLotListIterator = shipRequest.getLotList().iterator();
        ShippingRequestLot shipReqLot = null;

		try
		{
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall( "{ call sp_cancel_ship_request_details( ?,?,? ) }" );
            while( cancelshipReqLotListIterator.hasNext() )
            {
                shipReqLot = ( ShippingRequestLot )cancelshipReqLotListIterator.next();

                cstmt.setInt( 1, shipRequest.getShipRequestId() );
                cstmt.setInt( 2, shipReqLot.getLotId() );
                cstmt.setInt( 3, shipReqLot.getPulledQty() );

                cstmt.executeUpdate();
            }
            success = true;
		}
		catch( Exception e )
		{
            e.printStackTrace();
            Logger.error( "Error while Cancel the Ship Request lot details", e );
			throw new ScalarException ("Error while Cancel the Ship Request lot details");
		}
		finally
		{
            if ( dbTransaction == null )
            {

                if( success == true )
                {
                    try
                    {
                        conn.commit();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while commiting the Ship Request lot details save" );
                    }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while rolling back the Ship Request lot details save" );
                    }
                }
            }

            if (cstmt != null)
			{
				try { cstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
    }

    /**
     * To get the shipped lot status
     * @param dbTransaction
     * @param shippingRequest
     * @return
     * @throws ScalarException
     */
    public static boolean checkWhetherAnyLotIsShipped( DBTransaction dbTransaction, ShippingRequest shippingRequest ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean isLotShipped = false;
        try
        {
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT LOT_ID FROM SHIPPING_REQUEST_LOT_TABLE WHERE SHIP_REQ_ID = ? AND SHIP_REQ_LOT_STATUS = 'Closed'");
            pstmt.setInt( 1, shippingRequest.getShipRequestId() );
            rset = pstmt.executeQuery();
            if( rset.next() )
            {
                isLotShipped = true;
            }

        }
        catch ( SQLException se )
        {
            se.printStackTrace();
            throw new ScalarException ("Could not fetch the Ship Request Lot Status", se);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException excp){}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp){}
            }
        }
        return isLotShipped;

    }
}